package com.empresa.iflex.core;

import java.text.DecimalFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.empresa.iflex.database.IFlexDbAdapter;


public class Percurso{	

	private float distancia;		// Distancia percorrida
	private float consumo;			// Qtde de combustivel consumido no percurso
	private float preco;			// Preco por litro
	private String combustivel;		// Alcool ou Gasolina
	private String percurso;		// Cidade ou Estrada ou Misto(Cidade-Estrada)
	private Long data;				// Data da insercao no banco
	private long id;
	private int tanque_cheio = 0;
	
	private Context mCtx;
	
	public Percurso(Context ctx){
		this.mCtx = ctx;
	}
	
	public Percurso (Context ctx, float distancia, float consumo, float preco, 
			String combustivel, String percurso, Long data, int tanque_cheio){
		this.mCtx = ctx;
		this.distancia = distancia;
		this.consumo = consumo;
		this.preco = preco;
		this.combustivel = combustivel;
		this.percurso = percurso;
		this.setData(data);
		this.tanque_cheio = tanque_cheio;
	}	
	
	
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public float getPreco() {
		return preco;
	}

	public void setPreco(float preco) {
		this.preco = preco;
	}

	public String getCombustivel() {
		return combustivel;
	}

	public void setCombustivel(String combustivel) {
		this.combustivel = combustivel;
	}

	public String getPercurso() {
		return percurso;
	}

	public void setPercurso(String percurso) {
		this.percurso = percurso;
	}

	public double getDistancia() {
		return distancia;
	}

	public void setDistancia(float distancia) {
		this.distancia = distancia;
	}

	public double getConsumo() {
		return consumo;
	}

	public void setConsumo(float consumo) {
		this.consumo = consumo;
	}

	public Long getData() {
		return data;
	}

	// seta data do argumento ou data do sistema, caso argumento seja null
	public void setData(Long data) {
		if (data == null){
			this.data = null;
		}else{
			this.data = data;
		}		
	}	
	
	public void insert() throws Exception{
		
		ContentValues values = new ContentValues();
		String nullColumnHack = "data";
		float media = 0;
		values.put("distancia", this.distancia);
		values.put("consumo", this.consumo);
		values.put("combustivel", this.combustivel);
		values.put("percurso", this.percurso);
		values.put("preco", this.preco);
		values.put("tanque_cheio", tanque_cheio);
		
		DecimalFormat df = new DecimalFormat();
		df.setMaximumFractionDigits(2);
		
		if (tanque_cheio==1){
			media = (distancia/consumo);
		}
		values.put("media", media);
		
		String[] columns = new String[]{"_id"};
		
		IFlexDbAdapter ida = new IFlexDbAdapter(this.mCtx);
		SQLiteDatabase db = ida.wOpen();
		Cursor c_carro = db.query(IFlexDbAdapter.CARRO_TABLE_NAME, columns, "carro like 'Carro'", null, null, null, null);
		int id = 1;
		if (!c_carro.moveToFirst()){
			Log.w("Percurso.insert() ","Nenhum carro cadastrado");
		}else{
			id = c_carro.getInt(0); //obtem id do unico carro cadastrado
		}
		values.put("carro_id", id);
		
		db.insert(IFlexDbAdapter.PERCURSO_TABLE_NAME, nullColumnHack, values);
		
		db.close();
		ida.close();
	}
	
	
	public static void delete(SQLiteDatabase db, long id){
		
		String whereCLause = "_id = ?";
		String[] whereArgs = new String[]{Long.toString(id)};
		
		db.delete(IFlexDbAdapter.PERCURSO_TABLE_NAME, whereCLause, whereArgs);
	}
	
	public static int deleteAll(SQLiteDatabase db){
		return db.delete(IFlexDbAdapter.PERCURSO_TABLE_NAME, "1", null);
	}
	
	// retorna cursor com todas as tuplas
	public static Cursor findAll(SQLiteDatabase db){
		return db.rawQuery("SELECT _id, strftime('%d/%m/%Y %H:%M',data, 'localtime') " +
				"as datatime, distancia, preco, consumo, combustivel, percurso, ROUND(media,2) as media FROM percurso ORDER BY data desc", null);
	}
	
	public static Cursor findAllMedias(SQLiteDatabase db){
		return db.rawQuery("SELECT _id, " +
				"combustivel, percurso, ROUND(media,2) as media FROM percurso WHERE tanque_cheio=1 ORDER BY data asc", null);
	}
	
	public static float getBiggestMedia(SQLiteDatabase db){
		Cursor c = db.rawQuery("SELECT MAX(media) as maior FROM percurso WHERE tanque_cheio=1", null);
		c.moveToFirst();
		float maior = c.getFloat(c.getColumnIndex("maior"));
		c.close();
		return maior;		
	}
	
	public static float getLowerMedia(SQLiteDatabase db){
		Cursor c = db.rawQuery("SELECT MIN(media) as menor FROM percurso WHERE tanque_cheio=1", null);
		c.moveToFirst();
		float menor = c.getFloat(c.getColumnIndex("menor"));
		c.close();
		return menor;		
	}
	
	public static float getLastMedia(SQLiteDatabase db, String combustivel){
		Cursor c = db.rawQuery("SELECT ROUND(media,2) as media FROM percurso WHERE tanque_cheio=1 " +
									"AND combustivel like '"+combustivel+"' ORDER BY data desc", null);		
		c.moveToFirst();
		float ultima = c.getFloat(c.getColumnIndex("media"));
		c.close();
		return ultima;		
	}
	
	/*
	 * Distancia percorrida em cada mes
	 * SELECT strftime('%m/%Y', data) as month_year, SUM(distancia) as total_km FROM percurso GROUP BY month_year ORDER BY data;
	 * 
	 * Dados sobre gasolina
	 * SELECT month_year, t_gasolina, sp_gasolina, g_count FROM (
			SELECT strftime('%m/%Y', data) as month_year, SUM(consumo*preco) as t_gasolina, SUM(preco) as sp_gasolina, COUNT(*) as g_count 
				FROM percurso WHERE combustivel like 'gasolina' GROUP BY month_year);
		
		Dados sobre etanol
		SELECT month_year, t_etanol, sp_etanol, e_count FROM (
			SELECT strftime('%m/%Y', data) as month_year, SUM(consumo*preco) as t_etanol, SUM(preco) as sp_etanol, COUNT(*) as e_count 
				FROM percurso WHERE combustivel like 'etanol' GROUP BY month_year);
				
				SELECT strftime('%m/%Y', pg.data) as mes_ano, 
					ROUND(SUM (pg.consumo*pg.preco), 2) as t_gasolina, 
					ROUND(SUM(pe.consumo*pe.preco),2) as t_etanol,
					ROUND(SUM(pg.preco)/COUNT(*),2) as m_gasolina,
					ROUND(SUM(pe.preco)/COUNT(*),2) as m_etanol
				FROM percurso pg, percurso pe 
				WHERE strftime('%m/%Y', pg.data) = strftime('%m/%Y', pe.data) 
					AND  pg.combustivel like 'gasolina' 
					AND pe.combustivel like 'etanol' GROUP BY mes_ano;
	 */
	
	public static void unitTest01(Context ctx) throws Exception{
		for (int i=0; i<50; i++){
			int mes = 1 + (int)Math.round((Math.random() * (12 - 1)));
			int dia = 1 + (int)Math.round(Math.random() * (27 - 01));
			Date data_aux = new Date(2011, mes, dia);
			Percurso p = new Percurso(ctx, 260, 40, 1.699f,"etanol", "cidade", data_aux.getTime(), 1);
			p.insert();
			mes = 1 + (int)Math.round((Math.random() * (12 - 1)));
			dia = 1 + (int)Math.round(Math.random() * (27 - 01));
			data_aux = new Date(2011, mes, dia);
			
			p = new Percurso(ctx, 133, 13, 2.679f,"gasolina", "estrada", data_aux.getTime(), 1);;
			p.insert();
		}
	}
	
	// _id (mes_ano) | total_km | t_gasolina | m_gasolina | t_etanol | m_etanol
	public static Cursor groupByMonth(SQLiteDatabase db){
		//FIXME CUIDADO! Gambiarra GIGANTE!
		if (db.rawQuery("SELECT _id FROM percurso where combustivel like 'gasolina'", null).getCount() == 0){			
			return db.rawQuery("SELECT _id, strftime('%m/%Y', data) as mes_ano, ROUND(SUM(consumo*preco),2) as t_etanol, " +
					" ROUND(SUM(preco)/COUNT(*),2) as m_etanol, ROUND(SUM(distancia),2) as total_km, 0 as t_gasolina, 0 as m_gasolina "+
				" FROM percurso WHERE combustivel like 'etanol' GROUP BY mes_ano;", null);
			
		}else if (db.rawQuery("SELECT _id FROM percurso where combustivel like 'etanol'", null).getCount() == 0){			
			return db.rawQuery("SELECT _id, strftime('%m/%Y', data) as mes_ano, ROUND(SUM(consumo*preco),2) as t_gasolina, " +
					" ROUND(SUM(preco)/COUNT(*),2) as m_gasolina, ROUND(SUM(distancia),2) as total_km, 0 as t_etanol, 0 as m_etanol "+
					" FROM percurso WHERE combustivel like 'gasolina' GROUP BY mes_ano;", null);
			
		}else {
			return db.rawQuery("SELECT pe._id as _id, pg.month_year as mes_ano, d_gasolina+d_etanol as total_km, t_gasolina, m_gasolina, t_etanol, m_etanol FROM ("+
					" SELECT _id, strftime('%m/%Y', data) as month_year, ROUND(SUM(consumo*preco),2) as t_gasolina, ROUND(SUM(preco)/COUNT(*),2) as m_gasolina, ROUND(SUM(distancia),2) as d_gasolina "+
					" FROM percurso WHERE combustivel like 'gasolina' GROUP BY month_year) as pg LEFT OUTER JOIN "+
					"(SELECT _id, strftime('%m/%Y', data) as month_year, ROUND(SUM(consumo*preco),2) as t_etanol, ROUND(SUM(preco)/COUNT(*),2) as m_etanol, ROUND(SUM(distancia),2) as d_etanol "+
					" FROM percurso WHERE combustivel like 'etanol' GROUP BY month_year) as pe ON pg.month_year = pe.month_year GROUP BY pg.month_year", null);
		}
	}

}
